<?php
class SqliteDao extends Dao {
    protected function __construct($connSetting, $connName=DEFAULT_CONN) {
        parent::__construct($connSetting);
        $this->setConn($connName);
    }

    protected function getDsn($connSetting) {
        $sqliteVersion = strtolower($connSetting['DBTYPE']) == 'sqlite2' ? 'sqlite2' : 'sqlite';
        if(false == preg_match('/:/', $connSetting['DBNAME'])){
            $sqliteDb = DATABASE_DIR . '/' . $connSetting['DBNAME'];
        }
        $dirName = dirname($sqliteDb);
        if(false == is_dir($dirName)) @mkdir($dirName, 0777, true) or die('数据目录不存在且创建失败');
        if(DIRECTORY_SEPARATOR == '\\') $sqliteDb = str_replace('/', '\\', $sqliteDb);
        return $sqliteVersion . ':' . $sqliteDb;
    }

    public function getMetadata($tableName) {
        $tableName = $this->dbPrefix . $tableName;
        $tableKeys = $this->getTableKeys($tableName);
        $uniqueKeys = $tableKeys['uniqueKeys'];
        $notnullColumns = array();
        if (is_array($uniqueKeys)) {
            foreach ($uniqueKeys as $keyName => $columns) {
                $notnullColumns = array_merge($notnullColumns, $columns);
            }
            $notnullColumns = array_unique($notnullColumns);
        }
        $columnList = $this->getAll('pragma table_info(' . $tableName . ')');
        $columns = array();
        foreach ($columnList as $column) {
            $extra = 'PUMP_COL_TYPE_CUSTOM';
            $columnName = $column['name'];
            if(false !== stripos($column['type'], 'int')) {
                $columnLen = trim(strrchr($column['type'], '('), '()');
                $columnLen = $columnLen ? $columnLen : 8;
                $intType = preg_replace('/\(.+/', '', $column['type']);
                $columns[$columnName]['type'] = 'Number(' . $columnLen . ')';
            } else if (preg_match('/numeric|decimal|float|double/i', $column['type'])) {
                $columnLen = trim(strrchr($column['type'], '('), '()');
                $columns[$columnName]['type'] = 'Number(' . $columnLen . ')';
            } else if (preg_match('/char|text/i', $column['type'])) {
                $columLen = trim(strrchr($column['type'], '('), '()');
                if ($columLen) {
                    $columns[$columnName]['type'] = 'String(' . $columLen . ')';
                } else {
                    $columns[$columnName]['type'] = 'String';
                }
            } else if (false !== stripos($column['type'], 'date')) {
                $columns[$columnName]['type'] = ucfirst($column['type']);
            }
            $columns[$columnName]['notnull'] = $column['notnull'] || $column['pk'];
            if(false == $columns[$columnName]['notnull'] && in_array($columnName, $notnullColumns)) {
                throw new Exception('数据表"' . $tableName . '"的字段"' . $columnName . '"被唯一索引使用，建议此列设置为非空字段');
            }
            $columns[$columnName]['default'] = trim($column['dflt_value'], "'");
            if($column['default']) $columns[$columnName]['notnull'] = true;
            if ($column['pk']) {
                $tableKeys['primary'] = array($columnName);
                if ('integer' == $column['type']) {
                    $extra = 'PUMP_COL_TYPE_INCREMENT';
                } elseif (false !== stripos($columns[$columnName]['default'], COL_TYPE_UUID) ||   false !== stripos($columns[$columnName]['type'],'char(36)')) {
                    $extra = 'PUMP_COL_TYPE_UUID';
                }
                $columns[$columnName]['extra'] = $extra;
            }
            if (in_array($columnName, Table::$createTimeArray)) {
                $createAt = $columnName;
            }
            if (in_array($columnName, Table::$updateTimeArray)) {
                $updateAt = $columnName;
            }
        }
        $metadata = array('columns'=>$columns,
                          'create_at'=>$createAt,
                          'update_at'=>$updateAt,
                          'primaryKey'=>$tableKeys['primary'] ? $tableKeys['primary'] : array(),
                          'uniqueKeys'=>$tableKeys['uniqueKeys'] ? $tableKeys['uniqueKeys'] : array(),
                          'indexes'   =>$tableKeys['indexes'] ? $tableKeys['indexes'] : array());
        return $metadata;
    }

      

    private function getTableKeys($tableName) {
        $keyList = $this->getAll("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='$tableName' AND sql IS NOT NULL");
        $keys = array();
        foreach($keyList as $key){
            $keyName = $key['name'];
            $keySql = $key['sql'];
            preg_match('/create\s+(unique)?[^\(]+\((.+)\)/i', $keySql, $arr);
            if(strtolower($arr[1]) == 'unique'){
                $keys['uniqueKeys'][$keyName] = explode(',', $arr[2]);
            } else {
                $keys['indexes'][$keyName] = explode(',', $arr[2]);
            }
        }
        return $keys;
    }
    
    public function getAllTables() {
        if (0 == sizeof($this->tables)) {
            $tableList = $this->getAll("SELECT tbl_name FROM sqlite_master WHERE type='table'", PUMP_FETCH_NUM);
            foreach ($tableList as $row) {
                $this->tables[] = preg_replace('/^' . $this->dbPrefix . '/', '', strtolower($row[0]));
            }
        }
        
        return $this->tables;
    }

    public function createTable($tblName, $metadata) {
        $uniqueKeys = $metadata['uniqueKeys'];
        $notnullColumns = array();
        if (is_array($uniqueKeys)) {
            foreach ($uniqueKeys as $keyName => $columns) {
                $notnullColumns = array_merge($notnullColumns, $columns);
            }
            $notnullColumns = array_unique($notnullColumns);
        }
        $tableName = strtolower($this->dbPrefix . $tblName);
        $createQuery = 'CREATE TABLE ' . $tableName . '(';
        $columns = array();
        foreach ($metadata['columns'] as $columnName=>$column) {
            $columnLen = strrchr($column['type'], '(');
            if (COL_TYPE_UUID == $column['extra']) {
                $columnLen = '(36)';
                $column['default'] = COL_TYPE_UUID;
            }
            if (false !== stripos($column['type'], 'String')) {
                if ('' != $columnLen) {
                    $columnType = 'VARCHAR' . $columnLen;
                } else {
                    $columnType = 'text';
                }
            } elseif (false !== stripos($column['type'], 'Number')) {
                if(strrchr($columnLen, ',')) {
                    $columnType = 'numeric' . $columnLen;
                } else {
                    $columnType = 'integer';
                    if(false == in_array($columnName, $metadata['primaryKey'])) {
                        $columnType .= $columnLen;
                    }
                }
            } elseif (false !== stripos($column['type'], 'Date')) {
                $columnType = $column['type'];
            }
            $notnull = $column['notnull'] || in_array($columnName, $notnullColumns) ? 'NOT NULL' : '';
            $default = '';
            if ('' != $column['default']) {
                if (false !== stripos($column['type'], 'Number')) {
                    $default = 'DEFAULT ' . $column['default'];
                } else {
                    $default = "DEFAULT '" . $column['default'] . "'"; 
                }
            }
            $extra = '';
            if (in_array($columnName, $metadata['primaryKey'])) {
                $extra = 'primary key';
            }
            $columns[] = $columnName . ' ' . $columnType . ' ' . $notnull . '  ' . $default . ' ' . $extra;
        }
        $createQuery .= implode(',', $columns);
        $createQuery .= ')';
        
        if ($this->execute($createQuery)) {
            $this->tables[] = $tableName;
            $this->createKeys($tableName, $metadata['uniqueKeys'], $metadata['indexes']);
            return true;
        } else {
            throw new Exception('数据表"' . $tableName . '"创建失败，请手工创建<br />' . $createQuery);
        }
    }

    private function createKeys($tableName, $uniqueKeys, $indexKeys) {
        if (sizeof($uniqueKeys) > 0) {
            foreach($uniqueKeys as $keyName=>$keyCols) {
                $this->execute("CREATE UNIQUE INDEX {$keyName} ON {$tableName}(" . implode(',', $keyCols) . ")");
            }
        }

        if (sizeof($indexKeys) > 0) {
            foreach($indexKeys as $keyName=>$keyCols) {
                $this->execute("CREATE INDEX {$keyName} ON {$tableName}(" . implode(',', $keyCols) . ")");
            }
        }
    }

    public function vacuum() {
        return $this->execute('vacuum');
    }

    /**
     * 限制返回记录数
     */
    public function limit($strQuery, $limit) {
        return $strQuery . ' LIMIT ' . $limit;
    }
}
?>